from datetime import datetime
from itertools import chain
from collections import defaultdict

from sqlalchemy import func, select, exc, and_, distinct, literal, text
from app.config.db import DBUtils
from app.model.BusinessInfoModel import BusinessInfo
from app.model.actualcontrollerModel import ActualController
from app.model.FKListModel import fklist
from app.model.SKListModel import sklist
from app.timetest.timing_decorator import timing_decorator


# 图谱数据结构处理
@timing_decorator
def process_graph(result_list, label, type, sflx):
    group_fkje_nd = defaultdict(int)
    group_fkje_rel = defaultdict(int)
    label_set = set()
    gs_map = {
        '深研院':'深圳研究院',
        '集团公司':'集团总部',
        '航天科技财务有限责任公司':'财务公司',
        '档案馆':'航天档案馆',
        '系统院':'系统工程院',
        '资源卫星':'资源卫星中心'
    }
    gs_id = {11989570,11989569,11989571}  #(保定市乐凯化学有限公司,乐凯化学材料有限公司,四川乐凯新材料有限公司)三家公司ID
    if type == 'gs':
        for row in result_list:
            ys_gs = row['gs']
            if row['ID'] in gs_id:
                gs = '七院'
            else:
                gs = gs_map.get(ys_gs, ys_gs)
            key_nd = (row['cydw'], gs, row['ID'])
            key_rel = (row['cydw'], gs, row['ID'], row['lx'])
            group_fkje_nd[key_nd] += row['total']
            group_fkje_rel[key_rel] += row['total']
            if gs not in label_set:
                label_set.add(gs)

            # key_nd = (row.cydw, row.gs, row.ID)
            # group_fkje_nd[key_nd] += row.total
            # key_rel = (row.cydw, row.gs, row.ID, row.lx)
            # group_fkje_rel[key_rel] += row.total
            # if row.gs not in label_set:
            #     label_set.add(row.gs)
        node = [{
            'id': str(ID)+'cy' + sflx,
            'label': cydw,
            'nodeType': 'cydw',
            'amount': str(round(total / 10000, 2)) if total >= 50 or total == 0 else '0.01',
            'combo': gs + sflx
        } for (cydw, gs, ID), total in group_fkje_nd.items()]

        rel = [{
            'source': label if lx == 'FK' else str(ID)+'cy'+ sflx,
            'target': str(ID)+'cy'+sflx if lx == 'FK' else label,
            'label': str(round(total / 10000, 2)) if total >= 50 or total == 0 else '0.01',
            'elx': lx
        } for (cydw, gs, ID, lx), total in group_fkje_rel.items()]

        combos = [{
            'id': gs + sflx,
            'label': gs,
            'combo': 'cb1'
        } for gs in label_set]
    else:
        rel_set = set()
        combo_label = {}
        for row in result_list:
            key_nd = (row['wldw'], row['kzid'], row['id'])
            key_rel = (row['wldw'], row['kzr'], row['id'], row['lx'])
            group_fkje_nd[key_nd] += row['total']
            group_fkje_rel[key_rel] += row['total']
            # key_nd = (row['kzr'], row['kzid'])
            # if key_nd not in label_set:
            #     label_set.add(key_nd)
            k_rel = (row['kzid'], row['id'])
            if k_rel not in rel_set:
                rel_set.add(k_rel)
            l_kzid = row['kzid']
            l_kzr = row['kzr']
            combo_label[l_kzid] = l_kzr

        # wldw_nd = [{
        #     'id': str(id)+'wl',
        #     'label': wldw,
        #     'nodeType': 'wldw',
        #     'amount': str(round(total / 10000, 2)) if total >= 50 or total == 0 else '0.01',
        #     'combo': 'k' + str(kzid)
        # } for (wldw, kzid, id), total in group_fkje_nd.items()]
        seen_ids = set()
        wldw_nd = [
            node for node in [
                {
                    'id': str(id)+'wl',
                    'label': wldw,
                    'nodeType': 'wldw',
                    'amount': str(round(total / 10000, 2)) if total >= 50 or total == 0 else '0.01',
                    'combo': 'k' + str(kzid)
                }
                for (wldw, kzid, id), total in group_fkje_nd.items()
            ]
            if not (node['id'] in seen_ids or seen_ids.add(node['id']))
        ]
        sjkzr_nd = [{
            'id': str(l_kzid) + sflx,
            'label': l_kzr,
            'nodeType': 'sjkzr'
        } for l_kzid, l_kzr in combo_label.items()]

        # wldw_rel = [{
        #     'source': label if lx == 'FK' else str(id)+'wl',
        #     'target': str(id)+'wl' if lx == 'FK' else label,
        #     'label': str(round(total / 10000, 2)) if total >= 50 or total == 0 else '0.01',
        #     'elx': lx
        # } for (wldw, kzr, id, lx), total in group_fkje_rel.items()]
        seen_edges = set()
        wldw_rel = [
            edge for edge in [
                {
                    'source': label if lx == 'FK' else str(id)+'wl',
                    'target': str(id)+'wl' if lx == 'FK' else label,
                    'label': str(round(total / 10000, 2)) if total >= 50 or total == 0 else '0.01',
                    'elx': lx
                }
                for (wldw, kzr, id, lx), total in group_fkje_rel.items()
            ]
            if not ((edge['source'], edge['target'], edge['elx']) in seen_edges or seen_edges.add((edge['source'], edge['target'], edge['elx'])))
        ]
        sjkzr_rel = [{
            'source': str(kzid) + sflx,
            'target': str(id)+'wl'+ sflx,
            'elx': 'sjkzr'
        } for kzid, id in rel_set]

        combos = [{
            'id': 'k' + str(l_kzid) + sflx,
            'label': l_kzr
        } for l_kzid, l_kzr in combo_label.items()]

        node = wldw_nd + sjkzr_nd
        rel = wldw_rel + sjkzr_rel
    return node, rel, combos


class TradeCrowdService:
    def __init__(self):
        self.db_utils = DBUtils()
        self.current_year = datetime.now().year

    def select_trade_crowd_info(self, session, company, is_cydw, start_date, end_date):
        company_label = 'node22'
        combo = [{'id': 'cb1', 'label': '中国电子科技集团有限公司'}]
        fk_ywfl = fklist.JS_YWFL == '内部结算'  # 付款表排除同名户
        sk_ywfl = sklist.JS_YWFL == '内部结算'  # 收款表排除同名户
        condition_fk = fklist.SCRQ.between(start_date, end_date)
        condition_sk = sklist.SCRQ.between(start_date, end_date)
        if int(is_cydw) in range(1,4):
            company_node = [{'id': company_label, 'label': company, 'nodeType': 'cydw'}]
            # cydw_list = select(distinct(BusinessInfo.GSMC)).where(BusinessInfo.IS_CYDW == 1)
            if int(is_cydw) == 1:
                fk_gsmc = fklist.FKZHMC == company
                sk_gsmc = sklist.SKZHMC == company
                fk_param = "fkzhmc = :param"
                sk_param = "skzhmc = :param"
            elif int(is_cydw) == 2:
                fk_gsmc = fklist.FKGS == company
                sk_gsmc = sklist.SKGS == company
                fk_param = "fkgs = :param"
                sk_param = "skgs = :param"
            elif int(is_cydw) == 3:
                fk_gsmc = 1 == 1
                sk_gsmc = 1 == 1
                fk_param = "1 = 1"
                sk_param = "1 = 1"
            try:
                # 需判断SKZHMC是否成员单位
                fk_query_fklist = (
                    select(fklist.SKZHMC.label('cydw'),
                           fklist.SKGS.label('gs'),
                           BusinessInfo.ID,
                           literal('FK').label('lx'),
                           func.sum(fklist.FKJE).label('total'))
                    .join(BusinessInfo, BusinessInfo.GSMC == fklist.SKZHMC)
                    .where(and_(fk_gsmc, condition_fk, fk_ywfl, fklist.SKGS.isnot(None)))
                    .group_by(fklist.SKZHMC, fklist.SKGS, BusinessInfo.ID)
                    .order_by(func.sum(fklist.FKJE).desc())
                    .limit(5)
                )
                # fk_list1 = session.execute(fk_query_fklist).mappings()
                # fk_query_sklist = (
                #     select(sklist.SKZHMC.label('cydw'),
                #            sklist.SKGS.label('gs'),
                #            BusinessInfo.ID,
                #            literal('FK').label('lx'),
                #            func.sum(sklist.FKJE).label('total'))
                #     .join(BusinessInfo, BusinessInfo.GSMC == sklist.SKZHMC)
                #     .where(and_(sklist.FKZHMC == company, fk_dif_name))
                #     .group_by(sklist.SKZHMC, sklist.SKGS, BusinessInfo.ID)
                # )
                fk_list = session.execute(fk_query_fklist).mappings()
                if fk_list:
                    cydw_nd_fk, cydw_rel_fk, gs_combos_fk = process_graph(fk_list, company_label, 'gs', 'fk')
                else:
                    cydw_nd_fk = []
                    cydw_rel_fk = [{'source': company_label, 'target': 'cb1', 'elx': 'FK', 'label': 0}]
                    gs_combos_fk = []

                # sk_query_fklist = (
                #     select(fklist.FKZHMC.label('cydw'),
                #            fklist.FKGS.label('gs'),
                #            BusinessInfo.ID,
                #            literal('SK').label('lx'),
                #            func.sum(fklist.FKJE).label('total'))
                #     .join(BusinessInfo, BusinessInfo.GSMC == fklist.FKZHMC)
                #     .where(and_(fklist.SKZHMC == company, sk_dif_name))
                #     .group_by(fklist.FKZHMC, fklist.FKGS, BusinessInfo.ID)
                # )
                # sk_list1 = session.execute(sk_query_fklist).mappings()
                # 需判断FKZHMC是否为成员单位
                sk_query_sklist = (
                    select(sklist.FKZHMC.label('cydw'),
                           sklist.FKGS.label('gs'),
                           BusinessInfo.ID,
                           literal('SK').label('lx'),
                           func.sum(sklist.FKJE).label('total'))
                    .join(BusinessInfo, BusinessInfo.GSMC == sklist.FKZHMC)
                    .where(and_(sk_gsmc, condition_sk, sk_ywfl, sklist.FKGS.isnot(None)))
                    .group_by(sklist.FKZHMC, sklist.FKGS, BusinessInfo.ID)
                    .order_by(func.sum(sklist.FKJE).desc())
                    .limit(5)
                )
                sk_list = session.execute(sk_query_sklist).mappings()
                if sk_list:
                    cydw_nd_sk, cydw_rel_sk, gs_combos_sk = process_graph(sk_list, company_label, 'gs', 'sk')
                else:
                    cydw_nd_sk = []
                    cydw_rel_sk = [{'source': 'cb1', 'target': company_label, 'elx': 'SK', 'label': 0}]
                    gs_combos_sk = []

                # combine_list = chain(sk_list, fk_list)
                # if combine_list:
                #     cydw_nd, cydw_rel, gs_combos = process_graph(combine_list, company_label, 'gs')

                wldw_query_fk = text(f"""
                    select distinct name as kzr,skzhmc as wldw,gsid as id,kzid,total,'FK' as lx from (
                    select name,skzhmc,gsid,kzid,
                    count(distinct skzhmc) over (PARTITION BY name) as cnt,
                    sum(fkje) over (PARTITION BY name,skzhmc,gsid) as total
                    from ESUN.V_BI_GS_KZR_FKLIST
                    where {fk_param}
                    and scrq >= :s_date
                    and scrq <= :e_date                    
                    and is_cydw not in (1,2,3)
                    and js_ywfl = '外部结算')
                    where cnt >= 2
                    ORDER BY total DESC  -- 按总金额降序排序
                    FETCH FIRST 5 ROWS ONLY;                          
                """)
                params = {"param": company, "s_date": start_date, "e_date": end_date}
                wldw_fk_list = session.execute(wldw_query_fk, params).mappings()
                if wldw_fk_list:
                    wldw_nd_fk , wldw_rel_fk, sj_combos_fk = process_graph(wldw_fk_list, company_label, 'sjzkr', 'fk')
                else:
                    wldw_nd_fk = []
                    wldw_rel_fk = []
                    sj_combos_fk = []

                wldw_query_sk =text(f"""
                    select distinct name as kzr,fkzhmc as wldw,gsid as id,kzid,total,'SK' as lx from (
                    select name,fkzhmc,gsid,kzid,
                    count(distinct fkzhmc) over (PARTITION BY name) as cnt,
                    sum(fkje) over (PARTITION BY name,fkzhmc,gsid) as total
                    from ESUN.V_BI_GS_KZR_SKLIST
                    where {sk_param}
                    and scrq >= :s_date
                    and scrq <= :e_date                       
                    and is_cydw not in (1,2,3)
                    and js_ywfl = '外部结算')
                    where cnt >= 2   
                    ORDER BY total DESC  -- 按总金额降序排序
                    FETCH FIRST 5 ROWS ONLY;             
                """)
                wldw_sk_list = session.execute(wldw_query_sk, params).mappings()
                if wldw_fk_list:
                    wldw_nd_sk , wldw_rel_sk, sj_combos_sk = process_graph(wldw_sk_list, company_label, 'sjzkr', 'sk')
                else:
                    wldw_nd_sk = []
                    wldw_rel_sk = []
                    sj_combos_sk = []

                # wldw_list = chain(wldw_fk_list, wldw_sk_list)
                # if wldw_list:
                #     wldw_nd , wldw_rel, sj_combos = process_graph(wldw_list, company_label, 'sjzkr')

                nodes = cydw_nd_fk + cydw_nd_sk + wldw_nd_fk + wldw_nd_sk + company_node
                edges = cydw_rel_fk + cydw_rel_sk + wldw_rel_fk + wldw_rel_sk
                combos = combo + sj_combos_fk + sj_combos_sk + gs_combos_fk + gs_combos_sk
                seen = {}
                unique_combos = []
                # 遍历原始数组
                for item in combos:
                    # 创建基于 id 和 label 的唯一键
                    key = (item['id'], item['label'])
                    # 仅当该键第一次出现时添加元素
                    if key not in seen:
                        seen[key] = True  # 标记键已出现
                        unique_combos.append(item)  # 保留元素
                combos = unique_combos

                result = [{
                    'nodes': nodes,
                    'edges': edges,
                    'combos': combos
                }]
            except exc.SQLAlchemyError as e:
                error_response = [{'error': str(e)}]
                try:
                    self.db_utils.rollback()
                except Exception as rollback_error:
                    print(f"Failed to rollback transaction: {rollback_error}")
                return error_response
            finally:
                try:
                    self.db_utils.remove_session()
                except Exception as remove_error:
                    print(f"Failed to rollback transaction: {remove_error}")
        else:
            company_node = [{'id': company_label, 'label': company, 'nodeType': 'wldw'}]
            try:
                fk_query = (
                    select(fklist.FKZHMC.label('cydw'),
                           fklist.FKGS.label('gs'),
                           BusinessInfo.ID,
                           literal('SK').label('lx'),
                           func.sum(fklist.FKJE).label('total'))
                    .join(BusinessInfo, BusinessInfo.GSMC == fklist.FKZHMC)
                    .where(and_(fklist.SKZHMC == company, condition_fk, fklist.FKGS.isnot(None)))
                    .group_by(fklist.FKZHMC, fklist.FKGS, BusinessInfo.ID)
                )
                fk_list = session.execute(fk_query).mappings()
                if fk_list:
                    wldw_nd_fk, wldw_rel_fk, com_fk = process_graph(fk_list, company_label, 'gs', 'fk')
                else:
                    wldw_nd_fk = []
                    wldw_rel_fk = []
                    com_fk = [{'source': 'cb1', 'target': company_label, 'elx': 'FK', 'label': 0}]

                sk_query = (
                    select(sklist.SKZHMC.label('cydw'),
                           sklist.SKGS.label('gs'),
                           BusinessInfo.ID,
                           literal('FK').label('lx'),
                           func.sum(sklist.FKJE).label('total'))
                    .join(BusinessInfo, BusinessInfo.GSMC == sklist.SKZHMC)
                    .where(and_(sklist.FKZHMC == company, condition_sk, sklist.SKGS.isnot(None)))
                    .group_by(sklist.SKZHMC, sklist.SKGS, BusinessInfo.ID)
                )
                sk_list = session.execute(sk_query).mappings()
                if sk_list:
                    wldw_nd_sk, wldw_rel_sk, com_sk = process_graph(sk_list, company_label, 'gs', 'sk')
                else:
                    wldw_nd_sk = []
                    wldw_rel_sk = []
                    com_sk = [{'source': company_label, 'target': 'cb1', 'elx': 'SK', 'label': 0}]

                # pro_list = chain(sk_list, fk_list)
                # if pro_list:
                #     wldw_nd, wldw_rel, com = process_graph(pro_list, company_label, 'gs')

                # wldw_sub = select(ActualController.NAME).where(ActualController.GSMC == company).distinct()
                # wldw_list = select(ActualController.GSMC).where(ActualController.NAME.in_(wldw_sub))
                # comm_fk_query = select(fklist.FKZHMC,
                #                        fklist.SKZHMC,
                #                        func.sum(fklist.FKJE).label('total') \
                #                        .where(fklist.SKZHMC.in_(wldw_list)) \
                #                        .group_by(fklist.SKZHMC, fklist.FKZHMC)
                #                        )
                # comm_sk_query = select(sklist.FKZHMC,
                #                        sklist.SKZHMC,
                #                        func.sum(sklist.FKJE).label('total') \
                #                        .where(sklist.FKZHMC.in_(wldw_list)) \
                #                        .group_by(sklist.FKZHMC, sklist.SKZHMC)
                #                        )

                nodes = wldw_nd_fk + wldw_nd_sk + company_node
                combos = combo + com_fk + com_sk
                wldw_rel = wldw_rel_fk + wldw_rel_sk
                result = [{
                    'nodes': nodes,
                    'edges': wldw_rel,
                    'combos': combos
                }]
            except exc.SQLAlchemyError as e:
                error_response = [{'error': str(e)}]
                try:
                    self.db_utils.rollback()
                except Exception as rollback_error:
                    print(f"Failed to rollback transaction: {rollback_error}")
                return error_response
            finally:
                try:
                    self.db_utils.remove_session()
                except Exception as remove_error:
                    print(f"Failed to rollback transaction: {remove_error}")
        return result